﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlClient;

namespace DLDU
{
    public partial class FormMain : Form
    {
        SqlConnection connection = new SqlConnection();

        public FormMain()
        {
            InitializeComponent();
        }

        private void buttonAddServer_Click(object sender, EventArgs e)
        {
            addServer();
        }

        private void addServer()
        {
            string server = textBoxAddServer.Text.Trim();

            if (server.Length > 0)
            {
                if (!listBoxServers.Items.Contains(server))
                {
                    listBoxServers.Items.Add(server);
                    textBoxAddServer.Text = String.Empty;
                }
            }
        }

        private void buttonDeleteServer_Click(object sender, EventArgs e)
        {
            if (listBoxServers.SelectedItem != null)
            {
                listBoxServers.Items.Remove(listBoxServers.SelectedItem);
            }
        }

        private void buttonAddUser_Click(object sender, EventArgs e)
        {
            addUser();
        }

        private void addUser()
        {
            string user = textBoxAddUser.Text.Trim();

            if (user.Length > 0)
            {
                if (!listBoxUsers.Items.Contains(user))
                {
                    listBoxUsers.Items.Add(user);
                    textBoxAddUser.Text = String.Empty;
                }
            }
        }

        private void buttonDeleteUser_Click(object sender, EventArgs e)
        {
            if (listBoxUsers.SelectedItem != null)
            {
                listBoxUsers.Items.Remove(listBoxUsers.SelectedItem);
            }
        }

        private void buttonLoadServer_Click(object sender, EventArgs e)
        {
            openFileDialogAdd.Title = "Открыть файл с серверами";
            openFileDialogAdd.Filter = "txt files (*.txt)|*.txt|All files (*.*)|*.*";
            openFileDialogAdd.FilterIndex = 2;
            if (openFileDialogAdd.ShowDialog() == DialogResult.OK)
            {
                try
                {
                    if (openFileDialogAdd.FileName.Length != 0)
                    {
                        foreach (string item in File.ReadAllText(openFileDialogAdd.FileName).Split(new string[] { ";", " ", Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries))
                        {
                            if (item.Trim().Length != 0)
                            {
                                if (!listBoxServers.Items.Contains(item.Trim()))
                                {
                                    listBoxServers.Items.Add(item.Trim());
                                }
                            }
                        }
                    }
                }
                catch (Exception eX)
                {
                    MessageBox.Show(eX.Message);
                }
            }
        }

        private void buttonLoadUser_Click(object sender, EventArgs e)
        {
            openFileDialogAdd.Title = "Открыть файл с логинами";
            openFileDialogAdd.Filter = "txt files (*.txt)|*.txt|All files (*.*)|*.*";
            openFileDialogAdd.FilterIndex = 2;
            if (openFileDialogAdd.ShowDialog() == DialogResult.OK)
            {
                try
                {
                    if (openFileDialogAdd.FileName.Length != 0)
                    {
                        foreach (string item in File.ReadAllText(openFileDialogAdd.FileName).Split(new string[] { ";", " ", Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries))
                        {
                            if (item.Trim().Length != 0)
                            {
                                if (!listBoxUsers.Items.Contains(item.Trim()))
                                {
                                    listBoxUsers.Items.Add(item.Trim());
                                }
                            }
                        }
                    }
                }
                catch (Exception eX)
                {
                    MessageBox.Show(eX.Message);
                }
            }
        }

        private void buttonStartCleaning_Click(object sender, EventArgs e)
        {
            buttonStartCleaning.Enabled = false;
            List<string> servers = new List<string>();
            List<string> users = new List<string>();

            foreach (string item in listBoxServers.Items)
            {
                servers.Add(item.Trim());
            }

            foreach (string item in listBoxUsers.Items)
            {
                users.Add(item.Trim());
            }

            if (!(startCleaning(servers, users)))
            {

            }
            buttonStartCleaning.Enabled = true;
        }

        private bool startCleaning(List<string> servers, List<string> users)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = connection;
            if (connection.State == ConnectionState.Open) connection.Close();

            if (servers.Count > 0)
            {
                progressBarServers.Maximum = servers.Count;
                progressBarServers.Value = 0;
                foreach (string serverName in servers)
                {
                    progressBarServers.Value++;
                    connection.ConnectionString = "Data Source=" + serverName + "; Integrated Security=True; Connect Timeout=120;";

                    try
                    {
                        connection.Open();
                        writeLog(serverName + ". Статус подключения: " + connection.State.ToString());

                        List<string> bases = new List<string>();
                        bases = getBasesList();

                        if (bases.Count > 0)
                        {
                            progressBarBases.Maximum = bases.Count;
                            progressBarBases.Value = 0;
                            foreach (string baseName in bases)
                            {
                                progressBarBases.Value++;
                                if (users.Count > 0)
                                {
                                    progressBarUsers.Maximum = users.Count;
                                    progressBarUsers.Value = 0;
                                    foreach (string userName in users)
                                    {
                                        progressBarUsers.Value++;
                                        if (userName.Trim().Length > 0)
                                        {
                                            command.CommandText = "USE [" + baseName + "] DROP USER \"" + userName + "\"";
                                            try
                                            {
                                                command.ExecuteNonQuery();
                                                writeLog("Пользователь " + userName + " базы данных " + baseName + " удален.");
                                            }
                                            catch (Exception eX)
                                            {
                                                writeLog(eX.Message.ToString());
                                            }

                                            if (progressBarBases.Value == progressBarBases.Maximum)
                                            {
                                                command.CommandText = "DROP LOGIN \"" + userName + "\"";
                                                try
                                                {
                                                    command.ExecuteNonQuery();
                                                    writeLog("Имя входа " + userName + " сервера " + serverName + " удалено.");
                                                }
                                                catch (Exception eX)
                                                {
                                                    writeLog(eX.Message.ToString());
                                                }
                                            }
                                        }
                                    }
                                    progressBarUsers.Value = 0;
                                }
                                else
                                {
                                    writeLog("Список логинов пуст. Добавьте логины в список логинов.");
                                }
                            }
                            progressBarBases.Value = 0;
                        }
                        else
                        {
                            writeLog(serverName + ". Списов баз не загружен!");
                        }

                        connection.Close();
                        writeLog(serverName + ". Статус подключения: " + connection.State.ToString());
                    }
                    catch (Exception eX)
                    {
                        writeLog(eX.Message);
                        return false;
                    }
                }
                progressBarServers.Value = 0;
            }
            else
            {
                writeLog("Список серверов пуст. Добавьте сервера в список серверов.");
            }
            return true;
        }

        private void writeLog(string words)
        {
            if (words.Length != 0) this.textBoxLog.Text += DateTime.Now.ToString() + ": " + words + Environment.NewLine;
        }

        private List<string> getBasesList()
        {
            List<string> bases = new List<string>();

            SqlCommand command = new SqlCommand();
            SqlDataAdapter adapter = new SqlDataAdapter();
            DataSet dataSet = new DataSet();
            command.CommandText = "SELECT name FROM sys.databases";
            command.Connection = connection;
            dataSet.Tables.Add("Base");
            DataTable tableUser = dataSet.Tables["Base"];
            adapter.SelectCommand = command;
            try
            {
                adapter.Fill(dataSet, "Base");
                foreach (var item in tableUser.Rows)
                {
                    bases.Add(((System.Data.DataRow)(item)).ItemArray[0].ToString());
                }
                //comboBoxBase.DataSource = tableUser;
                //comboBoxBase.DisplayMember = tableUser.Columns[0].ColumnName;
                writeLog("Список баз загружен.");
                return bases;
            }
            catch (Exception eX)
            {
                writeLog(eX.Message.ToString());
                return bases;
            }
        }

        private void listBoxServers_DragEnter(object sender, DragEventArgs e)
        {
            e.Effect = DragDropEffects.All;
        }

        private void listBoxServers_DragDrop(object sender, DragEventArgs e)
        {
            string[] files = (string[])e.Data.GetData(DataFormats.FileDrop, false);
            foreach (string file in files)
            {
                if (Path.GetExtension(file) == ".txt")
                {
                    foreach (string item in File.ReadAllText(file).Split(new string[] { ";", " ", Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries))
                    {
                        if (item.Trim().Length != 0)
                        {
                            if (!listBoxServers.Items.Contains(item.Trim()))
                            {
                                listBoxServers.Items.Add(item.Trim());
                            }
                        }
                    }
                }
            }
        }

        private void listBoxUsers_DragEnter(object sender, DragEventArgs e)
        {
            e.Effect = DragDropEffects.All;
        }

        private void listBoxUsers_DragDrop(object sender, DragEventArgs e)
        {
            string[] files = (string[])e.Data.GetData(DataFormats.FileDrop, false);
            foreach (string file in files)
            {
                if (Path.GetExtension(file) == ".txt")
                {
                    foreach (string item in File.ReadAllText(file).Split(new string[] { ";", " ", Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries))
                    {
                        if (item.Trim().Length != 0)
                        {
                            if (!listBoxUsers.Items.Contains(item.Trim()))
                            {
                                listBoxUsers.Items.Add(item.Trim());
                            }
                        }
                    }
                }
            }
        }

        private void textBoxAddUser_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                addUser();
            }
        }

        private void textBoxAddServer_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                addServer();
            }
        }
    }
}
